package com.iplant.aps.serviceimpl.dao.wms;

import com.alibaba.fastjson.JSON;
import com.iplant.aps.service.mesenum.MESDBSource;
import com.iplant.aps.service.mesenum.MESException;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.aps.APSBOMItem;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.oms.OMSOrder;
import com.iplant.aps.service.po.sfc.SFCBOMTask;
import com.iplant.aps.service.po.sfc.SFCBOMTaskItem;
import com.iplant.aps.service.po.wms.WMSLineEdgeLibrary;
import com.iplant.aps.service.po.wms.WMSLineEdgeLibraryItem;
import com.iplant.aps.service.po.wms.WMSPickDemand;
import com.iplant.aps.service.po.wms.WMSPickDemandItem;
import com.iplant.aps.service.utils.Configuration;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import com.iplant.aps.serviceimpl.dao.erp.ERPUtils;
import com.iplant.aps.serviceimpl.dao.oms.OMSOrderDAO;
import com.iplant.aps.serviceimpl.utils.aps.APSConstans;
import com.iplant.aps.serviceimpl.utils.sap.ZIF_MM_GET_WMS281_WSStub;
import com.iplant.aps.shristool.CalendarDAO;
import org.apache.axis2.transport.http.HTTPConstants;
import org.apache.axis2.transport.http.impl.httpclient3.HttpTransportPropertiesImpl;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 线边库管理界面
 *
 * @author YouWang·Peng
 * 2022-3-28 10:54:37
 */
public class WMSLineEdgeLibraryDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(WMSLineEdgeLibraryDAO.class);

    private static WMSLineEdgeLibraryDAO Instance = null;

    private WMSLineEdgeLibraryDAO() {
        super();
    }

    public static WMSLineEdgeLibraryDAO getInstance() {
        if (Instance == null)
            Instance = new WMSLineEdgeLibraryDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, WMSLineEdgeLibrary wWMSLineEdgeLibrary, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wWMSLineEdgeLibrary == null)
                return 0;

            String wSQL = "";
            if (wWMSLineEdgeLibrary.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.wms_lineedgelibrary(MaterialID,MaterialName,"
                                + "MaterialNo,Total,FQTY,CreateTime,EditTime,UnitID,MinPacking,BatchNumber,Stock," +
                                "Supplier,ProductName,SerialNo,ProductTime,AreaID,SourceType,BOMNumber," +
                                "ProjectStockNo,SourceIDs) "
                                + "VALUES(:MaterialID,:MaterialName,"
                                + ":MaterialNo,:Total,:FQTY,:CreateTime,:EditTime,:UnitID,:MinPacking,:BatchNumber," +
                                ":Stock,:Supplier,:ProductName,:SerialNo,:ProductTime,:AreaID,:SourceType,:BOMNumber," +
                                ":ProjectStockNo,:SourceIDs);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format("UPDATE {0}.wms_lineedgelibrary SET MaterialID = :MaterialID,"
                                + "MaterialName = :MaterialName,MaterialNo = :MaterialNo,Total=:Total,FQTY = :FQTY,"
                                + "CreateTime = :CreateTime,EditTime = now(),"
                                + "UnitID=:UnitID,MinPacking=:MinPacking,BatchNumber=:BatchNumber,Stock=:Stock," +
                                "Supplier=:Supplier,ProductName=:ProductName,SerialNo=:SerialNo," +
                                "ProductTime=:ProductTime,AreaID=:AreaID,SourceType=:SourceType,BOMNumber=:BOMNumber," +
                                "ProjectStockNo=:ProjectStockNo,SourceIDs=:SourceIDs " +
                                "WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wWMSLineEdgeLibrary.ID);
            wParamMap.put("MaterialID", wWMSLineEdgeLibrary.MaterialID);
            wParamMap.put("MaterialName", wWMSLineEdgeLibrary.MaterialName);
            wParamMap.put("MaterialNo", wWMSLineEdgeLibrary.MaterialNo);
            wParamMap.put("FQTY", wWMSLineEdgeLibrary.FQTY);
            wParamMap.put("CreateTime", wWMSLineEdgeLibrary.CreateTime);
            wParamMap.put("EditTime", wWMSLineEdgeLibrary.EditTime);
            wParamMap.put("UnitID", wWMSLineEdgeLibrary.UnitID);
            wParamMap.put("MinPacking", wWMSLineEdgeLibrary.MinPacking);
            wParamMap.put("Total", wWMSLineEdgeLibrary.Total);
            wParamMap.put("BatchNumber", wWMSLineEdgeLibrary.BatchNumber);
            wParamMap.put("Stock", wWMSLineEdgeLibrary.Stock);
            wParamMap.put("Supplier", wWMSLineEdgeLibrary.Supplier);
            wParamMap.put("ProductName", wWMSLineEdgeLibrary.ProductName);
            wParamMap.put("SerialNo", wWMSLineEdgeLibrary.SerialNo);
            wParamMap.put("ProductTime", wWMSLineEdgeLibrary.ProductTime);
            wParamMap.put("AreaID", wWMSLineEdgeLibrary.AreaID);
            wParamMap.put("SourceType", wWMSLineEdgeLibrary.SourceType);
            wParamMap.put("BOMNumber", wWMSLineEdgeLibrary.BOMNumber);
            wParamMap.put("ProjectStockNo", wWMSLineEdgeLibrary.ProjectStockNo);
            wParamMap.put("SourceIDs", wWMSLineEdgeLibrary.SourceIDs);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wWMSLineEdgeLibrary.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wWMSLineEdgeLibrary.setID(wResult);
            } else {
                wResult = wWMSLineEdgeLibrary.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<WMSLineEdgeLibrary> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (WMSLineEdgeLibrary wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.wms_lineedgelibrary WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public WMSLineEdgeLibrary SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        WMSLineEdgeLibrary wResult = new WMSLineEdgeLibrary();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<WMSLineEdgeLibrary> wList = SelectList(wLoginUser, wID, -1, "", null, null,
                    -1, -1, "", wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);

            wResult.ItemList = WMSLineEdgeLibraryItemDAO.getInstance().SelectList(wLoginUser, -1, wResult.ID, -1, -1,
                    -1, -1, -1, -1, -1, null, null, wErrorCode);
            // 排序
            wResult.ItemList.sort(Comparator.comparing(WMSLineEdgeLibraryItem::getCreateTime,
                    Comparator.reverseOrder()));
            wResult.ItemList.forEach(p -> {
                p.UnitID = wList.get(0).UnitID;
                p.UnitText = wList.get(0).UnitText;
                p.MinPacking = wList.get(0).MinPacking;
                p.MaterialName = wList.get(0).MaterialName;
                p.MaterialNo = wList.get(0).MaterialNo;
            });
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<WMSLineEdgeLibrary> SelectList(BMSEmployee wLoginUser, int wID, int wMaterialID, String wMaterial,
                                               Calendar wStartTime, Calendar wEndTime, int wAreaID, int wSourceType,
                                               String wBatchNumber,
                                               OutResult<Integer> wErrorCode) {
        List<WMSLineEdgeLibrary> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            if (wMaterial == null) {
                wMaterial = "";
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }

            String wSQL = StringUtils.Format("SELECT t.*,t1.Name AreaName,t2.MinPacking RealMinPacking FROM {0}" +
                            ".wms_lineedgelibrary t "
                            + " left join {0}.bms_department t1 on t.AreaID=t1.ID "
                            + " left join {0}.mss_material t2 on t.MaterialID=t2.ID "
                            + "WHERE  1=1  "
                            + "and ( :wID <= 0 or :wID = t.ID ) "
                            + "and ( :AreaID <= 0 or :AreaID = t.AreaID ) "
                            + "and ( :SourceType <= 0 or :SourceType = t.SourceType ) "
                            + "and ( :wMaterialID <= 0 or :wMaterialID = " +
                            "t.MaterialID ) "
                            + "and ( :wBatchNumber is null or :wBatchNumber = '''' or t.BatchNumber=:wBatchNumber ) "
                            + "and ( :wMaterial is null or :wMaterial = '''' or (t.MaterialNo like ''%{1}%'' or " +
                            "t.MaterialName like ''%{1}%'') ) "
                            + "and ( :wStartTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or (:wStartTime <=  " +
                            "t.EditTime and :wEndTime >=  t.CreateTime) ) "
                            + "and ( :wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or (:wStartTime <=  " +
                            "t.EditTime and :wEndTime >=  t.CreateTime) );",
                    wInstance.Result, wMaterial);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wMaterialID", wMaterialID);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            wParamMap.put("wMaterial", wMaterial);
            wParamMap.put("AreaID", wAreaID);
            wParamMap.put("SourceType", wSourceType);
            wParamMap.put("wBatchNumber", wBatchNumber);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);

            SetValue(wResultList, wQueryResult, wSDF, wBaseTime1);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    private void SetValue(List<WMSLineEdgeLibrary> wResultList, List<Map<String, Object>> wQueryResult,
                          SimpleDateFormat wSDF, Calendar wBaseTime1) {
        try {
            for (Map<String, Object> wReader : wQueryResult) {
                WMSLineEdgeLibrary wItem = new WMSLineEdgeLibrary();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.UnitID = StringUtils.parseInt(wReader.get("UnitID"));
                wItem.SourceType = StringUtils.parseInt(wReader.get("SourceType"));
                wItem.AreaID = StringUtils.parseInt(wReader.get("AreaID"));
                wItem.AreaName = StringUtils.parseString(wReader.get("AreaName"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.Total = StringUtils.parseDouble(wReader.get("Total"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));

                wItem.Total = new BigDecimal(wItem.Total)
                        .setScale(2, RoundingMode.HALF_UP).doubleValue();
                wItem.FQTY = new BigDecimal(wItem.FQTY)
                        .setScale(2, RoundingMode.HALF_UP).doubleValue();

                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.EditTime = StringUtils.parseCalendar(wReader.get("EditTime"));
                wItem.MinPacking = StringUtils.parseDouble(wReader.get("RealMinPacking"));
                wItem.BOMNumber = StringUtils.parseDouble(wReader.get("BOMNumber"));

                wItem.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wItem.Stock = StringUtils.parseString(wReader.get("Stock"));
                wItem.Supplier = StringUtils.parseString(wReader.get("Supplier"));
                wItem.ProductName = StringUtils.parseString(wReader.get("ProductName"));
                wItem.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wItem.ProductTime = StringUtils.parseCalendar(wReader.get("ProductTime"));
                wItem.ProjectStockNo = StringUtils.parseString(wReader.get("ProjectStockNo"));
                wItem.SourceIDs = StringUtils.parseString(wReader.get("SourceIDs"));

                wItem.CreateTimeText = wItem.CreateTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.CreateTime.getTime())
                        : "";
                wItem.EditTimeText = wItem.EditTime.compareTo(wBaseTime1) > 0 ? wSDF.format(wItem.EditTime.getTime())
                        : "";
                wItem.UnitText = APSConstans.GetCFGUnitName(wItem.UnitID);

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 条件查询集合
     */
    public List<WMSLineEdgeLibrary> WMS_QueryListByPartAndSourceType(BMSEmployee wLoginUser, int wPartID,
                                                                     int wSourceType,
                                                                     OutResult<Integer> wErrorCode) {
        List<WMSLineEdgeLibrary> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            String wSQL = StringUtils.Format("SELECT t.*,t1.Name AreaName,t2.MinPacking RealMinPacking FROM {0}" +
                            ".wms_lineedgelibrary t "
                            + " left join {0}.bms_department t1 on t.AreaID=t1.ID "
                            + " left join {0}.mss_material t2 on t.MaterialID=t2.ID "
                            + "WHERE  t.AreaID IN (SELECT WorkAreaID FROM {0}.lfs_workareastation " +
                            "WHERE StationID=:StationID AND ACTIVE=1) AND t.SourceType=:SourceType AND t.FQTY>0;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("StationID", wPartID);
            wParamMap.put("SourceType", wSourceType);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);

            SetValue(wResultList, wQueryResult, wSDF, wBaseTime1);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 获取物料信息-单位、最小包装等
     *
     * @param wLoginUser  登录信息
     * @param wMaterialID 物料ID
     * @param wErrorCode  错误信息
     * @return 物料信息
     */
    public WMSLineEdgeLibrary GetMaterialInfo(BMSEmployee wLoginUser, int wMaterialID, OutResult<Integer> wErrorCode) {
        WMSLineEdgeLibrary wResult = new WMSLineEdgeLibrary();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT CYUnitID,MinPacking FROM {0}.mss_material where ID=:ID;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wMaterialID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.UnitID = StringUtils.parseInt(wReader.get("CYUnitID"));
                wResult.MinPacking = StringUtils.parseDouble(wReader.get("MinPacking"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * ERP过账
     */
    public ZIF_MM_GET_WMS281_WSStub.ZFMM_GET_WMS281Response ERPUse(BMSEmployee wLoginUser, APSBOMItem wAPSBOMItem,
                                                                   SFCBOMTask wSFCBOMTask,
                                                                   SFCBOMTaskItem wSFCBOMTaskItem,
                                                                   WMSLineEdgeLibrary wWMSLineEdgeLibrary,
                                                                   OutResult<Integer> wErrorCode) {
        ZIF_MM_GET_WMS281_WSStub.ZFMM_GET_WMS281Response wResult =
                new ZIF_MM_GET_WMS281_WSStub.ZFMM_GET_WMS281Response();
        try {
            ZIF_MM_GET_WMS281_WSStub service = new ZIF_MM_GET_WMS281_WSStub();
            // 认证代码 start
            HttpTransportPropertiesImpl.Authenticator auth = new HttpTransportPropertiesImpl.Authenticator();

            auth.setUsername(Configuration.readConfigString("erpPostingUser", "config/config"));
            auth.setPassword(Configuration.readConfigString("erpPostringPassword", "config/config"));

            service._getServiceClient().getOptions().setProperty(HTTPConstants.AUTHENTICATE, auth);
            // 认证代码 end
            ZIF_MM_GET_WMS281_WSStub.ZFMM_GET_WMS281 wZFMM_GET_WMS281 = new ZIF_MM_GET_WMS281_WSStub.ZFMM_GET_WMS281();

            ZIF_MM_GET_WMS281_WSStub.TABLE_OF_ZSGET_GR281HD_IN wTABLE_OF_ZSGET_GR281HD_IN = ERPUtils.getInstance()
                    .getTABLE_OF_ZSGET_GR281HD_IN(wAPSBOMItem, wSFCBOMTask, wSFCBOMTaskItem);
            wZFMM_GET_WMS281.setHD_INPUT(wTABLE_OF_ZSGET_GR281HD_IN);

            ZIF_MM_GET_WMS281_WSStub.TABLE_OF_ZSGET_GR281IT_IN wTABLE_OF_ZSGET_GR281IT_IN = ERPUtils.getInstance()
                    .getTABLE_OF_ZSGET_GR281IT_IN(wAPSBOMItem, wSFCBOMTask, wSFCBOMTaskItem, wWMSLineEdgeLibrary);
            wZFMM_GET_WMS281.setIT_INPUT(wTABLE_OF_ZSGET_GR281IT_IN);

            wZFMM_GET_WMS281.setIT_OUTPUT(new ZIF_MM_GET_WMS281_WSStub.TABLE_OF_ZWMS_DOCOUT());

            String wINputStr = JSON.toJSONString(wZFMM_GET_WMS281);
            ZIF_MM_GET_WMS281_WSStub.ZFMM_GET_WMS281Response zFMM_GET_WMS281 =
                    service.zFMM_GET_WMS281(wZFMM_GET_WMS281);
            String wOutPutStr = JSON.toJSONString(zFMM_GET_WMS281);

            //打印日志
            OMSOrder wOrder = OMSOrderDAO.getInstance().SelectByID(BaseDAO.SysAdmin, wSFCBOMTask.OrderID, wErrorCode);
            CalendarDAO.getInstance().WriteLogFile(wINputStr + "@@@@@@@@@@" + wOutPutStr, "ERP过账", wOrder,
                    wSFCBOMTask.Code);
            //维护过账是否成功的信息

            wResult = zFMM_GET_WMS281;
//            if (String.valueOf(zFMM_GET_WMS281.getIT_OUTPUT().getItem()[0].getZOK()).equals("N")) {
//                wSFCBOMTask.IsPost = 2;
//                wSFCBOMTask.PostMsg = String.valueOf(zFMM_GET_WMS281.getIT_OUTPUT().getItem()[0].getZMSG());
//            } else if (String.valueOf(zFMM_GET_WMS281.getIT_OUTPUT().getItem()[0].getZOK()).equals("Y")) {
//                wSFCBOMTask.IsPost = 1;
//                wSFCBOMTask.PostMsg = "过账成功";
//            }
//            SFCBOMTaskDAO.getInstance().Update(wLoginUser, wSFCBOMTask, wErrorCode);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public Map<Integer, Double> WMS_QueryQuotaMap(BMSEmployee wLoginUser, int wProductID, int wLineID,
                                                  List<Integer> wMaterialIDList, OutResult<Integer> wErrorCode) {
        Map<Integer, Double> wResult = new HashMap<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wMaterialIDList == null || wMaterialIDList.size() <= 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT MaterialID,FQTY FROM {0}.sfc_batchbomitem WHERE " +
                            "ProductID=:ProductID " +
                            "AND LineID=:LineID AND MaterialID IN ({1});",
                    wInstance.Result, StringUtils.Join(",", wMaterialIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ProductID", wProductID);
            wParamMap.put("LineID", wLineID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wMaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                double wFQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wResult.put(wMaterialID, wFQTY);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     *
     */
    public void ERPUse(BMSEmployee wLoginUser, APSBOMItem wAPSBOMItem, WMSPickDemand wWMSPickDemand,
                       WMSPickDemandItem wWMSPickDemandItem, WMSLineEdgeLibrary wWMSLineEdgeLibrary,
                       WMSLineEdgeLibraryItem wWMSLineEdgeLibraryItem,
                       OutResult<Integer> wErrorCode) {
        try {
            //项目库存查询
            String wProjectStockNo =
                    WMSLineEdgeLibraryDAO.getInstance().WMS_QueryProjectStockNoByBatchNumberAndMatieral(wLoginUser,
                            wWMSLineEdgeLibrary, wErrorCode);
            if (StringUtils.isNotEmpty(wProjectStockNo)) {
                wWMSLineEdgeLibrary.ProjectStockNo = wProjectStockNo;
            }

            ZIF_MM_GET_WMS281_WSStub service = new ZIF_MM_GET_WMS281_WSStub();
            // 认证代码 start
            HttpTransportPropertiesImpl.Authenticator auth = new HttpTransportPropertiesImpl.Authenticator();

            auth.setUsername(Configuration.readConfigString("erpPostingUser", "config/config"));
            auth.setPassword(Configuration.readConfigString("erpPostringPassword", "config/config"));

            service._getServiceClient().getOptions().setProperty(HTTPConstants.AUTHENTICATE, auth);
            // 认证代码 end
            ZIF_MM_GET_WMS281_WSStub.ZFMM_GET_WMS281 wZFMM_GET_WMS281 = new ZIF_MM_GET_WMS281_WSStub.ZFMM_GET_WMS281();

            ZIF_MM_GET_WMS281_WSStub.TABLE_OF_ZSGET_GR281HD_IN wTABLE_OF_ZSGET_GR281HD_IN = ERPUtils.getInstance()
                    .getTABLE_OF_ZSGET_GR281HD_IN(wAPSBOMItem, wWMSPickDemand, wWMSPickDemandItem);
            wZFMM_GET_WMS281.setHD_INPUT(wTABLE_OF_ZSGET_GR281HD_IN);

            ZIF_MM_GET_WMS281_WSStub.TABLE_OF_ZSGET_GR281IT_IN wTABLE_OF_ZSGET_GR281IT_IN = ERPUtils.getInstance()
                    .getTABLE_OF_ZSGET_GR281IT_IN(wAPSBOMItem, wWMSPickDemand, wWMSPickDemandItem,
                            wWMSLineEdgeLibrary, wWMSLineEdgeLibraryItem);
            wZFMM_GET_WMS281.setIT_INPUT(wTABLE_OF_ZSGET_GR281IT_IN);

            wZFMM_GET_WMS281.setIT_OUTPUT(new ZIF_MM_GET_WMS281_WSStub.TABLE_OF_ZWMS_DOCOUT());

            String wINputStr = JSON.toJSONString(wZFMM_GET_WMS281);
            ZIF_MM_GET_WMS281_WSStub.ZFMM_GET_WMS281Response zFMM_GET_WMS281 =
                    service.zFMM_GET_WMS281(wZFMM_GET_WMS281);
            String wOutPutStr = JSON.toJSONString(zFMM_GET_WMS281);

            //打印日志
            OMSOrder wOrder = OMSOrderDAO.getInstance().SelectByID(BaseDAO.SysAdmin, wAPSBOMItem.OrderID,
                    wErrorCode);
            CalendarDAO.getInstance().WriteLogFile(wINputStr + "@@@@@@@@@@" + wOutPutStr, "ERP过账", wOrder,
                    wWMSPickDemand.DemandNo);

            //维护线边库过账信息
            if (wWMSLineEdgeLibraryItem != null && wWMSLineEdgeLibraryItem.ID > 0) {
                if (String.valueOf(zFMM_GET_WMS281.getIT_OUTPUT().getItem()[0].getZOK()).equals("N")) {
                    wWMSLineEdgeLibraryItem.IsPost = 2;
                    wWMSLineEdgeLibraryItem.PostMsg =
                            String.valueOf(zFMM_GET_WMS281.getIT_OUTPUT().getItem()[0].getZMSG());
                } else if (String.valueOf(zFMM_GET_WMS281.getIT_OUTPUT().getItem()[0].getZOK()).equals("Y")) {
                    wWMSLineEdgeLibraryItem.IsPost = 1;
                    wWMSLineEdgeLibraryItem.PostMsg = "过账成功";
                }
                WMSLineEdgeLibraryItemDAO.getInstance().Update(wLoginUser, wWMSLineEdgeLibraryItem, wErrorCode);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    private String WMS_QueryProjectStockNoByBatchNumberAndMatieral(BMSEmployee wLoginUser,
                                                                   WMSLineEdgeLibrary wWMSLineEdgeLibrary,
                                                                   OutResult<Integer> wErrorCode) {
        String wResult = "";
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t.ProjectStockNo FROM {0}.`wms_pickdemanditemstock` t " +
                            "LEFT JOIN {0}.`wms_pickdemanditem` t1 ON t.TaskID=t1.ID " +
                            "WHERE  t1.MaterialID=:MaterialID AND t.BatchNumber=:BatchNumber LIMIT 1;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("MaterialID", wWMSLineEdgeLibrary.MaterialID);
            wParamMap.put("BatchNumber", wWMSLineEdgeLibrary.BatchNumber);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseString(wReader.get("ProjectStockNo"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<SFCBOMTaskItem> WMS_QueryUsedBOMTaskItemList(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        List<SFCBOMTaskItem> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t1.* FROM {0}.sfc_bomtaskitem t1,{0}.sfc_bomtask t2 " +
                            "WHERE t1.sfcbomtaskid=t2.ID AND t2.covatype=1 AND t2.status=2;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                SFCBOMTaskItem wItem = new SFCBOMTaskItem();

                wItem.BOMItemID = StringUtils.parseInt(wReader.get("BOMItemID"));
                wItem.MaterialNumber = StringUtils.parseDouble(wReader.get("MaterialNumber"));

                wResult.add(wItem);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取反冲料来源单ID
     */
    public String WMS_QueryReciolSourceIDs(BMSEmployee wLoginUser, String materialNo, String batchNumber,
                                           OutResult<Integer> wErrorCode) {
        String wResult = "";
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT DISTINCT t1.DemandID FROM {0}.`wms_pickdemanditem` t1," +
                            "{0}.`wms_pickdemanditemstock`t2,{0}.`wms_pickdemand` t3 " +
                            "WHERE t1.ID=t2.TaskID AND t1.MaterialNo=:MaterialNo AND t2.batchnumber=:batchnumber " +
                            "AND t3.ID=t1.DemandID AND t3.Status=4 ORDER BY t1.DemandID ASC;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("MaterialNo", materialNo);
            wParamMap.put("batchnumber", batchNumber);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            List<Integer> wIDList = new ArrayList<>();
            for (Map<String, Object> wReader : wQueryResult) {
                int wDemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wIDList.add(wDemandID);
            }
            wResult = StringUtils.Join(",", wIDList);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取批量偶换件来源单ID
     */
    public String WMS_QueryBatchSourceIDs(BMSEmployee wLoginUser, String materialNo, String batchNumber,
                                          OutResult<Integer> wErrorCode) {
        String wResult = "";
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT DISTINCT t1.DemandID FROM {0}.`wms_batchpickdemanditem` " +
                            "t1,{0}.`wms_batchpickdemanditemstock` t2,{0}.`wms_batchpickdemand` t3 " +
                            "WHERE t1.ID=t2.TaskID AND t1.MaterialNo=:MaterialNo AND t2.batchnumber=:batchnumber " +
                            "AND t3.ID=t1.DemandID AND t3.Status=4 ORDER BY t1.DemandID ASC;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("MaterialNo", materialNo);
            wParamMap.put("batchnumber", batchNumber);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            List<Integer> wIDList = new ArrayList<>();
            for (Map<String, Object> wReader : wQueryResult) {
                int wDemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wIDList.add(wDemandID);
            }
            wResult = StringUtils.Join(",", wIDList);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取反冲物料消耗车辆信息列表
     */
    public List<WMSLineEdgeLibraryItem> WMS_QueryWMSLineEdgeLibraryItemByMateiralNoAndBatchNumber(BMSEmployee wLoginUser,
                                                                                                  String materialNo,
                                                                                                  String batchNumber,
                                                                                                  OutResult<Integer> wErrorCode) {
        List<WMSLineEdgeLibraryItem> wResult = new ArrayList<WMSLineEdgeLibraryItem>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t1.ID,t3.OrderID,t3.PartNo,t3.PartID,t1.PartPointID,t1" +
                            ".MaterialID,t1.FQTY,t1.BOMNumber,t1.PickedNumber,t3.DeliveryTime  " +
                            "FROM {0}.`wms_pickdemanditem` t1,{0}.`wms_pickdemanditemstock`t2,{0}" +
                            ".`wms_pickdemand` t3  " +
                            "WHERE t1.ID=t2.TaskID AND t1.MaterialNo=:MaterialNo AND t2.batchnumber=:batchnumber " +
                            "AND t3.ID=t1.DemandID AND t3.Status=4 AND t1.PickedNumber>0 ORDER BY t3.PartNo ASC;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("MaterialNo", materialNo);
            wParamMap.put("batchnumber", batchNumber);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                WMSLineEdgeLibraryItem wItem = new WMSLineEdgeLibraryItem();

                wItem.PickDemandItemID = StringUtils.parseInt(wReader.get("ID"));
                wItem.OrderID = StringUtils.parseInt(wReader.get("OrderID"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("PickedNumber"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("DeliveryTime"));

                wResult.add(wItem);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<Integer> WMS_QueryToHandleDataList(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT DISTINCT t.ID FROM {0}.`wms_pickdemanditemstock` t " +
                            "LEFT JOIN {0}.`wms_pickdemanditem` t1 ON t.TaskID=t1.ID " +
                            "LEFT JOIN {0}.`wms_pickdemand` t2 ON t1.DemandID=t2.ID " +
                            "LEFT JOIN {0}.`mss_material` t3 ON t1.MaterialID=t3.ID " +
                            "WHERE t2.CreateTime>=''2023-11-24'' AND t2.CreateTime<''2023-12-15 14:00'' AND t3" +
                            ".Recoil=1;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.add(StringUtils.parseInt(wReader.get("ID")));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
